package com.sxfq.loansupermarket.backsystem.modular.marketing.controller;

import com.sxfq.loansupermarket.backsystem.common.constant.tips.Tip;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.exception.BizExceptionEnum;
import com.sxfq.loansupermarket.backsystem.common.exception.BussinessException;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Role;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.User;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.DateTimeKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.DateUtil;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.core.util.ToolUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.ApprovalAuditInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.ApprovalProcessInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerPublicService;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.MaxAuditLevelChannelInfo;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.PayServicedInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.ReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.SearchReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.SessionInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IReservationsService;
import com.sxfq.loansupermarket.backsystem.modular.refunds.service.impl.ResubmitLock;
import com.sxfq.loansupermarket.backsystem.modular.system.transfer.UserDto;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.service.IConsultantService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author py
 * @Date 2018年4月19日23:15:54
 */
@Controller
@RequestMapping("/reservations")
public class ReservationsController extends BaseController {
    @Autowired
    IReservationsService reservationsService;
    @Autowired
    private RedisTemplate redisTemplate;
    @Autowired
    ICustomerService customerService;

    @Autowired
    IConsultantService service;

    @Autowired
    ICustomerPublicService customerPublicService;

    @Autowired
    private SqlMapper sqlMapper;

    private String PREFIX = "/marketing/reservations/";

    /**
     * 跳转到市场预约  建档
     */
    @RequestMapping("/reservationsRecord")
    public String reservationsRecord(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "reservationsRecord.html";
    }

    /**
     * 跳转到市场补单界面
     */
    @RequestMapping(value = "/supplement/{id}")
    public String supplement(@PathVariable String id, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select ( " +
                " CASE t.customer_sex " +
                "  WHEN 1 THEN " +
                "   '男' " +
                "  WHEN 2 THEN " +
                "   '女' " +
                "  ELSE " +
                "   '未知' " +
                "  END " +
                "  ) as sex,t.* from reservations_info t where id='" + id + "' limit 0,1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        model.addAttribute("customerInfo", customerInfo);
        model.addAttribute("reservationsId", id);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "replenishment.html";
    }

    /**
     * 市场补单
     */
    @RequestMapping(value = "/supplementok", method = RequestMethod.POST)
    @ResponseBody
    public int supplementok(String oneChannel, String twoChannel, String thrChannel, String archiving, String filingTime, String reservationsId, String userName, Model model) {
        String sql = "select id from reservations_infosc a where id='" + reservationsId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        if (customerInfo == null) {
            String sql1 = "select * from reservations_info a where id='" + reservationsId + "'";
            CustomerInfo info = sqlMapper.selectOne(sql1, CustomerInfo.class);
            String sql11 = "select id,treatment_unit from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"" + "customer_info a where reservations_id='" + reservationsId + "' order by arrival_time limit 0,1";
            CustomerInfo info1 = sqlMapper.selectOne(sql11, CustomerInfo.class);
            //修改到店表渠道媒介
            String sqlupd = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"" + "customer_info a set one_channel='" + oneChannel + "',two_channel='" + twoChannel + "',thr_channel='" + thrChannel + "'," +
                    "one_medium='" + oneChannel + "',two_medium='" + twoChannel + "',thr_medium='" + thrChannel + "',scarchiving='" + archiving + "'," +
                    "update_time='" + DateTimeKit.now() + "',update_people='" + ShiroKit.getUser().getAccount() + "' " +
                    " where id='" + reservationsId + "'";
            sqlMapper.update(sqlupd);
            //设置预约号
            String getheadsql = "select firstconcats(account) as account from user where account='" + archiving + "'";
            User user = sqlMapper.selectOne(getheadsql, User.class);
            String currentDate = DateUtil.getTime(new Date()).substring(0, 10);//取今天的年月日
            String sqlre = "select * from reservations_info where left(filing_time,19) like '%" + currentDate + "%'";
            List<ReservationsInfo> rList = sqlMapper.selectList(sqlre, ReservationsInfo.class);
            String reservationsNum = user.getAccount().replace("_", "") + currentDate.replace("-", "") + (rList.size() + 1);
            info.setReservationsNum(reservationsNum);
            //修改总表渠道媒介
            String sqlup = "update reservations_info a set one_channel='" + oneChannel + "',two_channel='" + twoChannel + "',thr_channel='" + thrChannel + "'," +
                    "one_medium='" + oneChannel + "',two_medium='" + twoChannel + "',thr_medium='" + thrChannel + "',appointment_pay_service='" + info1.getTreatmentUnit() + "'," +
                    "access_disease='" + info1.getTreatmentUnit() + "',archiving='" + archiving + "',belonger='" + archiving + "',follower='" + archiving + "'," +
                    "department='" + oneChannel + "',filing_time='" + filingTime + "'," +
                    "update_time='" + DateTimeKit.now() + "',update_people='" + ShiroKit.getUser().getAccount() + "',reservations_num='" + reservationsNum + "' " +
                    " where id='" + reservationsId + "'";
            sqlMapper.update(sqlup);
            String sqlint = "INSERT INTO `supplement_log` ( `logtype` ,`customer_name`,`customer_phone`, `single_person`,`userid`, `classname`, `method`, " +
                    "`createtime`, `succeed`, `message`) VALUES" +
                    " ( '补单','" + info.getCustomerName() + "','" + info.getCustomerPhone() + "','" + ShiroKit.getUser().getAccount() + "', "
                    + ShiroKit.getUser().getId() + ", '" + ShiroKit.getUser().getAccount() + "补单 电话：" + info.getCustomerPhone() + " 姓名："
                    + info.getCustomerName() + " id:" + info.getId() + "', 'supplement', '" + DateTimeKit.now() + "', '成功', '"
                    + ShiroKit.getUser().getAccount() + "');";
            sqlMapper.insert(sqlint);
            String sqlin = "INSERT INTO reservations_infosc (`id`,`huifang`,`department`,`appointment_time`,`tc_name`,`first_visit_date`,`age_range`," +
                    "`phone_shar_ship`,`outpatient_name`, `customer_name`, `customer_phone`, `customer_sex`, `customer_age`, `qq_number`," +
                    " `wechat_number`,`other_contact`, `country`, `province`, `cities`, `region`, `quarters`,reservations_num," +
                    " `marital_status`, `occupation`, `archiving`, belonger,follower,`filing_time`, `entry_clerk`,`update_people`,`update_time`, " +
                    " `one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`,`remarks`, `status`,`appointment_arrival_time`," +
                    "`is_supplement`,identification_number,arrival_store,szm,money,deal_status,comment_status,statuss,appointment_pay_service," +
                    " access_disease,search_field,customer_sex_head) VALUES" +
                    "('" + info.getId() + "','" + info.getHuifang() + "','" + info.getOneChannel() + "','" + info.getAppointmentTime() + "','" + info.getTcName()
                    + "','" + info.getAppointmentArrivalTime() + "','" + info.getAgeRange() + "','" + info.getPhoneSharShip() + "','"
                    + info.getArrivalStore() + "','" + info.getCustomerName() + "','" + info.getCustomerPhone() + "'," + info.getCustomerSex() + ","
                    + info.getCustomerAge() + ",'" + info.getQqNumber() + "','" + info.getWechatNumber() + "','" + info.getOtherContact() + "','"
                    + info.getCountry() + "','" + info.getProvince() + "','" + info.getCities() + "','" + info.getRegion() + "','" + info.getQuarters() + "','"
                    + info.getReservationsNum() + "'," + info.getMaritalStatus() + ",'" + info.getOccupation() + "','" + archiving + "','" + archiving + "','" + archiving
                    + "','" + filingTime + "','" + info.getEntryClerk() + "','" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "'," +
                    "'" + oneChannel + "','" + twoChannel + "','" + thrChannel + "','" + info.getOneChannel() + "','" + twoChannel
                    + "','" + thrChannel + "','" + info.getRemarks() + "', '" + info.getStatus() + "','" + info.getAppointmentArrivalTime() + "','否','"
                    + info.getIdentificationNumber() + "','" + info.getArrivalStore() + "',firstconcats('" + info.getCustomerName() + "'),'" + info.getMoney() + "'," +
                    "'" + info.getDealStatus() + "','" + info.getCommentStatus() + "','待定','" + info1.getTreatmentUnit() + "'," +
                    "'" + info1.getTreatmentUnit() + "'," +
                    "concat('" + info.getCustomerName() + "','" + info.getCustomerPhone() + "',firstconcats('" + info.getCustomerName() + "')," +
                    "'" + info.getCustomerPhone2() + "','" + info.getWechatNumber() + "','" + DateTimeKit.now() + "','" + info.getMedicalRecordNumber() + "'),'"
                    + customerPublicService.getSexPt(info) + "');";
            return sqlMapper.insert(sqlin);
        } else {
            return 0;
        }
    }

    private final static Object PRESENT = new Object();

    /**
     * 修改到店客户
     */
    @RequestMapping(value = "/add_isRevisit")
    @ResponseBody
    public int addIsRevisit(@Valid CustomerInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String key = "/add_isRevisit";
        key = key + info.getFollower() + info.getCustomerPhone() + info.getAppointmentTime();
        //执行锁
        boolean lock = false;
        try {
            //设置解锁key
            lock = ResubmitLock.getInstance().lock(key, PRESENT);
            if (lock) {
                //放行
                return reservationsService.addIsRevisit(info);
            } else {
                //响应重复提交异常
                return 0;
            }
        } finally {
            //设置解锁key和解锁时间
            ResubmitLock.getInstance().unLock(lock, key, 10);
        }
    }

    /**
     * 修改到店客户
     */
    @RequestMapping(value = "/add_isRevisits")
    @ResponseBody
    public int add_isRevisits(@Valid CustomerInfo info, BindingResult result){
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String key = "/add_isRevisits";
        key = key + info.getFollower() + info.getCustomerPhone() + info.getAppointmentTime() + info.getRevisitContent();
        //执行锁
        boolean lock = false;
        try {
            //设置解锁key
            lock = ResubmitLock.getInstance().lock(key, PRESENT);
            if (lock) {
                //放行
                return reservationsService.add_isRevisits(info);
            } else {
                //响应重复提交异常
                return 0;
            }
        } finally {
            //设置解锁key和解锁时间
            ResubmitLock.getInstance().unLock(lock, key, 3);
        }
    }

    /**
     * 跳转到预约客户管理添加页面
     */
    @RequestMapping(value = "/reservations_add")
    public String reservationsAdd(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "reservations_add.html";
    }

    @RequestMapping(value = "/to_archiving")
    public String to_archiving(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "archiving.html";
    }

    @RequestMapping(value = "/to_archiving2")
    public String to_archiving2(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "archivings.html";
    }

    //查询业绩归属人
    @RequestMapping(value = "/get_belonger")
    public String get_belonger(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "belonger.html";
    }

    @RequestMapping(value = "/to_follower")
    public String to_follower(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "follower.html";
    }

    @RequestMapping(value = "/to_followers")
    public String to_followers(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "followers.html";
    }

    @RequestMapping(value = "/to_belongers")
    public String to_belongers(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "belongers.html";
    }

    @RequestMapping(value = "/to_belonger")
    public String to_belonger(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "belonger.html";
    }

    @RequestMapping(value = "/to_arrBelonger")
    public String to_arrBelonger(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "arrBelonger.html";
    }

    @RequestMapping(value = "/getHuiFang", method = RequestMethod.POST)
    @ResponseBody
    public String getHuiFang(String id) throws Exception {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select id as id,huifang as huifang from reservations_info where id='" + id + "' limit 0,1";
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sql, ReservationsInfo.class);
        String huifang = "";
        if (reservationsInfo != null) {
            huifang = reservationsInfo.getHuifang();
        }
        return huifang;
    }

    @RequestMapping(value = "/getTotleMoneys", method = RequestMethod.POST)
    @ResponseBody
    public Double getTotleMoneys(String id) throws Exception {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        Double actualAccountBalance = 0.00;
        String sql2 = "select actual_account_balance as actualAccountBalance " +
                "from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " t where t.reservations_id='" + id + "' limit 0,1";
        PayServicedInfo info = sqlMapper.selectOne(sql2, PayServicedInfo.class);
        if (info != null) {
            actualAccountBalance += info.getActualAccountBalance();
        }
        return actualAccountBalance;
    }

    @RequestMapping(value = "/getArchiving", method = RequestMethod.GET)
    @ResponseBody
    public List<UserDto> getArchiving(String roleId) throws Exception {
        if (StringUtil.isNotEmpty(roleId)) {
            String sql = "select * from user where roleid like '%" + roleId + "%' group by id;";
            List<UserDto> list = sqlMapper.selectList(sql, UserDto.class);
            return list;
        } else {
            String sql = "select * from user where roleid in " +
                    "(select id from role a where a.pid=(select id from role a where a.name='市场')" +
                    "union all select id from role a where a.name='市场') group by id;";
            List<UserDto> list = sqlMapper.selectList(sql, UserDto.class);
            return list;
        }
    }

    @RequestMapping(value = "/funs", method = RequestMethod.GET)
    @ResponseBody
    public Model funs(Model model) {
        String sql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + "";
        List<CustomerInfo> getList = sqlMapper.selectList(sql, CustomerInfo.class);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("getList", getList);
        return model;
    }

    /**
     * 删除预约客户
     */
    @RequestMapping(value = "/deleteReservation")
    @ResponseBody
    public Tip deleteReservation(@Valid String id) throws Exception {
        ReservationsInfo reservations = reservationsService.selectOneById(id);
        reservationsService.insertDeleteLog(reservations);
        reservationsService.deleteReservation(id);
        return SUCCESS_TIP;
    }

    /**
     * 跳转修改渠道
     */
    @RequestMapping(value = "/reservations_modifyChannel/{ido}")
    public String reservations_modifyChannel(Model model, @PathVariable String ido) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        try {
            String id = ido.split(",")[0];
            String outpatientName = ido.split(",")[1];
            ReservationsInfo reservationsInfo = reservationsService.selectOneById2(id);
            model.addAttribute("outpatientName", outpatientName);
            model.addAttribute("reservationsInfo", reservationsInfo);
            model.addAttribute("reservationsId", id);
            model.addAttribute("user", ShiroKit.getUser().getAccount());
            Random rand = new Random();
            int randomNumber = rand.nextInt(999999);
            model.addAttribute("num", randomNumber);
            if ("到店".equals(reservationsInfo.getStatus())) {
                return PREFIX + "reservations_modifyChannel.html";
            } else {
                return PREFIX + "reservations_modifyChannelw.html";
            }
        } catch (Exception e) {
            return "/errorClose.html";
        }
    }

    /**
     * 发起二次开发跳转界面
     */
    @RequestMapping(value = "/second_development/{id}")
    public String secondDevelopment(Model model, @PathVariable String id) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        try {
            ReservationsInfo reservationsInfo = reservationsService.selectOneById2(id);
            model.addAttribute("reservationsInfo", reservationsInfo);
            model.addAttribute("reservationsId", id);
            model.addAttribute("user", ShiroKit.getUser().getAccount());
            Random rand = new Random();
            int randomNumber = rand.nextInt(99999999);
            model.addAttribute("num", randomNumber);
            return PREFIX + "reservations_second_development.html";
        } catch (Exception e) {
            return "/errorClose.html";
        }
    }

    /**
     * 二次开发提交
     */
    @RequestMapping(value = "/insert_second_development", method = RequestMethod.POST)
    @ResponseBody
    public int updateChannel(String name, String reservationsId, String follower, String belonger,
                             String oneChannel,String twoChannel, String thrChannel, String remarks, String customerName, String customerPhone, String outpatientName,String num) throws Exception {
        String operationTime = DateTimeKit.now();
        String id=num+operationTime;
        String sql = "INSERT INTO reservations_second_development (id,reservations_id,name,follower,belonger,one_channel,two_channel,thr_channel,operation_time,remarks,customer_name ,customer_phone,submitter,outpatient_name,channel_audit_level) VALUES" +
                "('"+id+"','" + reservationsId + "','" + name + "','" + follower + "','" + belonger + "','" + oneChannel + "','" + twoChannel + "','" + thrChannel + "','" + operationTime + "','" + remarks + "'," +
                "'" + customerName + "','" + customerPhone + "', '" + ShiroKit.getUser().getAccount() + "','"+outpatientName+"','1');";
        sqlMapper.insert(sql);
        String sql1 = "select id from reservations_second_development where operation_time='" + operationTime + "' and reservations_id='" + reservationsId + "'";
        ApprovalProcessInfo info = sqlMapper.selectOne(sql1, ApprovalProcessInfo.class);
        String upd = "update approval_process_iconinfo set process_id='" + info.getId() + "' ,num='' where num='" + num + "'";
        sqlMapper.update(upd);
        return 1;
    }

    /**
     * 审核跳转修改渠道
     */
    @RequestMapping(value = "/reservations_modify/{ids}")
    public String reservations_modify(Model model, @PathVariable String ids) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        try {
            String id = ids.split(",")[0];
            String reservationsId = ids.split(",")[1];
            ReservationsInfo reservationsInfo = reservationsService.selectOneById2(reservationsId);
            String sql = "select id as id,customer_name as customerName,customer_phone as customerPhone,remarks as remarks,s_one_channel as sOneChannel," +
                    "s_two_channel as sTwoChannel," +
                    "s_thr_channel as sThrChannel,user as user,name as name,follower as follower,xfollower as xfollower," +
                    "belonger as belonger,xbelonger as xbelonger,one_channel as oneChannel,two_channel as twoChannel,channel_audit_level as channelAuditLevel," +
                    "thr_channel as thrChannel,left(operation_time,19) as operationTime,submitter as submitter,status as status," +
                    "(CASE result " +
                    "  WHEN 1 THEN " +
                    "  '审核通过' " +
                    "  WHEN 2 THEN " +
                    "  '审核未通过' " +
                    "  ELSE " +
                    "  '审核进行中' " +
                    "  END " +
                    " ) as result from approval_process_info where id = '" + id + "' limit 1";
            ApprovalProcessInfo info = sqlMapper.selectOne(sql, ApprovalProcessInfo.class);
            String sql1 = "select account from user where channel_audit_level = '" + info.getChannelAuditLevel() + "' limit 1";
            User info1 = sqlMapper.selectOne(sql1, User.class);
            if (("审核进行中").equals(info.getResult())) {
                info.setReviewerPeople("(" + info1.getAccount() + ")");
            } else {
                info.setReviewerPeople("");
            }
            //图片
            String sql2 = "select * from approval_process_iconinfo where process_id=" + id + ";";
            List<ApprovalProcessInfo> info2 = sqlMapper.selectList(sql2, ApprovalProcessInfo.class);
            info.setApprovalProcessInfoList(info2);
            String sqlaud = "select id as id ,auditor as auditor,left(time,19) as time,remarks as remarks,if(status=1,'已同意','拒绝') as status " +
                    "from approval_audit_info where approval_id = '" + id + "'";
            List<ApprovalAuditInfo> approvalAuditInfoList = sqlMapper.selectList(sqlaud, ApprovalAuditInfo.class);
            model.addAttribute("reservationsInfo", reservationsInfo);
            model.addAttribute("approvalAuditInfoList", approvalAuditInfoList);
            model.addAttribute("info", info);
            return PREFIX + "reservations_modify.html";
        } catch (Exception e) {
            return "/errorClose.html";
        }
    }

    /**
     * 审核跳转修改渠道
     */
    @RequestMapping(value = "/reservations_modifyj/{ids}")
    public String reservations_modifyj(Model model, @PathVariable String ids) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        try {
            String id = ids.split(",")[0];
            String reservationsId = ids.split(",")[1];
            ReservationsInfo reservationsInfo = reservationsService.selectOneById2(reservationsId);
            String sql = "select id as id,customer_name as customerName,customer_phone as customerPhone,remarks as remarks,s_one_channel as sOneChannel," +
                    "s_two_channel as sTwoChannel," +
                    "s_thr_channel as sThrChannel,user as user,name as name,follower as follower,xfollower as xfollower," +
                    "belonger as belonger,xbelonger as xbelonger,one_channel as oneChannel,two_channel as twoChannel,channel_audit_level as channelAuditLevel," +
                    "thr_channel as thrChannel,left(operation_time,19) as operationTime,submitter as submitter,status as status," +
                    "(CASE result " +
                    "  WHEN 1 THEN " +
                    "  '审核通过' " +
                    "  WHEN 2 THEN " +
                    "  '审核未通过' " +
                    "  ELSE " +
                    "  '审核进行中' " +
                    "  END " +
                    " ) as result from approval_process_info where id = '" + id + "' limit 1";
            ApprovalProcessInfo info = sqlMapper.selectOne(sql, ApprovalProcessInfo.class);
            String sql1 = "select account from user where channel_audit_level = '" + info.getChannelAuditLevel() + "' limit 1";
            User info1 = sqlMapper.selectOne(sql1, User.class);
            if (("审核进行中").equals(info.getResult())) {
                info.setReviewerPeople("(" + info1.getAccount() + ")");
            } else {
                info.setReviewerPeople("");
            }
            //图片
            String sql2 = "select * from approval_process_iconinfo where process_id=" + id + ";";
            List<ApprovalProcessInfo> info2 = sqlMapper.selectList(sql2, ApprovalProcessInfo.class);
            info.setApprovalProcessInfoList(info2);
            String sqlaud = "select id as id ,auditor as auditor,left(time,19) as time,remarks as remarks,if(status=1,'已同意','拒绝') as status " +
                    "from approval_audit_info where approval_id = '" + id + "'";
            List<ApprovalAuditInfo> approvalAuditInfoList = sqlMapper.selectList(sqlaud, ApprovalAuditInfo.class);
            model.addAttribute("reservationsInfo", reservationsInfo);
            model.addAttribute("approvalAuditInfoList", approvalAuditInfoList);
            model.addAttribute("info", info);
            return PREFIX + "reservations_modifyj.html";
        } catch (Exception e) {
            return "/errorClose.html";
        }
    }

    /**
     * 审核跳转修改渠道详情
     */
    @RequestMapping(value = "/reservations_modifys/{ids}")
    public String reservations_modifys(Model model, @PathVariable String ids) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        try {
            String id = ids.split(",")[0];
            String sql = "select id as id,customer_name as customerName,customer_phone as customerPhone,remarks as remarks," +
                    "s_one_channel as sOneChannel,s_two_channel as sTwoChannel," +
                    "s_thr_channel as sThrChannel,user as user,name as name,follower as follower,xfollower as xfollower," +
                    "belonger as belonger,xbelonger as xbelonger,one_channel as oneChannel,two_channel as twoChannel,channel_audit_level as channelAuditLevel," +
                    "thr_channel as thrChannel,left(operation_time,19) as operationTime,submitter as submitter,status as status," +
                    "(CASE result " +
                    "  WHEN 1 THEN " +
                    "  '审核通过' " +
                    "  WHEN 2 THEN " +
                    "  '审核未通过' " +
                    "  ELSE " +
                    "  '审核进行中' " +
                    "  END " +
                    " ) as result from approval_process_info where id = '" + id + "' limit 1";
            ApprovalProcessInfo info = sqlMapper.selectOne(sql, ApprovalProcessInfo.class);
            String sql1 = "select account from user where channel_audit_level = '" + info.getChannelAuditLevel() + "' limit 1";
            User info1 = sqlMapper.selectOne(sql1, User.class);
            if (("审核进行中").equals(info.getResult())) {
                info.setReviewerPeople("(" + info1.getAccount() + ")");
            } else {
                info.setReviewerPeople("");
            }
            //图片
            String sql2 = "select * from approval_process_iconinfo where process_id=" + id + ";";
            List<ApprovalProcessInfo> info2 = sqlMapper.selectList(sql2, ApprovalProcessInfo.class);
            info.setApprovalProcessInfoList(info2);
            String sqlaud = "select id as id ,auditor as auditor,left(time,19) as time,remarks as remarks,if(status=1,'已同意','拒绝') as status " +
                    "from approval_audit_info where approval_id = '" + id + "'";
            List<ApprovalAuditInfo> approvalAuditInfoList = sqlMapper.selectList(sqlaud, ApprovalAuditInfo.class);
            model.addAttribute("approvalAuditInfoList", approvalAuditInfoList);
            model.addAttribute("info", info);
            return PREFIX + "reservations_modifys.html";
        } catch (Exception e) {
            return "/errorClose.html";
        }
    }

    /**
     * 审核渠道方案二提交
     */
    @RequestMapping(value = "/updateModity1", method = RequestMethod.POST)
    @ResponseBody
    public int updateChannel1(String oneChannel, String reservationsId, String id, String outpatientName, String archiving, String xfollower, String xbelonger,
                              String twoChannel, String thrChannel, String remarks, String customerName, String customerPhone) throws Exception {
        String sql = "update reservations_info a set a.one_channel='" + oneChannel + "',a.two_channel='" + twoChannel
                + "',a.archiving='" + archiving + "',a.follower='" + xfollower + "',a.belonger='" + xbelonger + "'," +
                "a.thr_channel='" + thrChannel + "',a.one_medium='" + oneChannel + "',a.two_medium='" + twoChannel + "',a.thr_medium='" + thrChannel + "'," +
                "department='" + oneChannel + "' where id='" + reservationsId + "'";
        String sq13 = "INSERT INTO `approval_process_log_info` (`logtype`,`reservations_id`, `customer_name`,`customer_phone`,`person`, `createtime`,`succeed`,`name`,`remarks`,content,outpatient_name) VALUES " +
                " ( '同意申请', '" + reservationsId + "', '" + customerName + "', '" + customerPhone + "', '" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "'," +
                "'成功','" + archiving + "','" + remarks + "','" + ShiroKit.getUser().getAccount() + " 把 " + customerName + " 的客户渠道审核通过:','" + outpatientName + "');";
        String maxlevel = "select id from max_audit_level_channel_info where max_level=" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"ChannelAuditLevel",0) + "";
        MaxAuditLevelChannelInfo max = sqlMapper.selectOne(maxlevel, MaxAuditLevelChannelInfo.class);
        if (max != null) {
            String updateSqlt = "update approval_process_info a set a.result=1 where id='" + id + "' ";
            sqlMapper.update(sql);
            String selesql = "select id from reservations_infosc where id='" + reservationsId + "' limit 1";
            SessionInfo sessionInfo = sqlMapper.selectOne(selesql, SessionInfo.class);
            if (sessionInfo == null) {
                String insersql = "insert into reservations_infosc select * from reservations_info where id='" + reservationsId + "'";
                sqlMapper.insert(insersql);
            } else {
                String sqlsc = "update reservations_infosc a set a.one_channel='" + oneChannel + "',a.two_channel='" + twoChannel
                        + "',a.archiving='" + archiving + "',a.follower='" + xfollower + "',a.belonger='" + xbelonger + "'," +
                        "a.thr_channel='" + thrChannel + "',a.one_medium='" + oneChannel + "',a.two_medium='" + twoChannel + "',a.thr_medium='" + thrChannel + "'," +
                        "department='" + oneChannel + "' where id='" + reservationsId + "'";
                sqlMapper.update(sqlsc);
            }
            String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' and status=1  ";
            List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);  //到过的店
            for (int i = 0; i < abbreviationList.size(); i++) {
                String head = abbreviationList.get(i);
                String upcussql = "update " + head + "customer_info a set a.one_channel='" + oneChannel + "',a.two_channel='" + twoChannel +
                        "',a.thr_channel='" + thrChannel + "',a.scarchiving='" + xbelonger + "',a.one_medium='" + oneChannel + "'" +
                        ",a.two_medium='" + twoChannel + "',a.thr_medium='" + thrChannel + "',department='" + oneChannel + "' " +
                        " where reservations_id='" + reservationsId + "'";
                sqlMapper.update(upcussql);
            }
            sqlMapper.update(updateSqlt);
        } else {
            String updateSqlt = "update approval_process_info set channel_audit_level =channel_audit_level+1 where id='" + id + "' ";
            sqlMapper.update(updateSqlt);
        }
        String insql = "insert into approval_audit_info (approval_id,auditor,remarks,status) values ('" + id + "','" + ShiroKit.getUser().getAccount() + "','" + remarks + "',1)";
        sqlMapper.insert(insql);
        sqlMapper.insert(sq13);
        return 1;
    }

    /**
     * 渠道审核不同意提交
     */
    @RequestMapping(value = "/updateModity3", method = RequestMethod.POST)
    @ResponseBody
    public int updateChannel3(String reservationsId, String id, String remarks, String outpatientName, String customerName, String customerPhone, String archiving) throws Exception {
        String updateSqlt = "update approval_process_info a set a.result='2' where id='" + id + "';";
        sqlMapper.update(updateSqlt);
        String insertsql = "INSERT INTO approval_audit_info (approval_id,auditor, remarks, status) VALUES (" + id + ",'" + ShiroKit.getUser().getAccount() + "', '" + remarks + "', 2);";
        sqlMapper.insert(insertsql);
        String sq13 = "INSERT INTO `approval_process_log_info` (`logtype`,`reservations_id`, `customer_name`,`customer_phone`,`person`, `createtime`,`succeed`,`name`,`remarks`,content,outpatient_name) VALUES " +
                " ( '审核未通过', '" + reservationsId + "', '" + customerName + "', '" + customerPhone + "', '" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "'," +
                "'成功','" + archiving + "','" + remarks + "','" + ShiroKit.getUser().getAccount() + " 把 " + customerName + " 的客户渠道审核未通过:','" + outpatientName + "');";
        return sqlMapper.insert(sq13);
    }

    /**
     * 修改
     */
    @RequestMapping(value = "/update/{cri}")
    @ResponseBody
    public int update(@Valid ReservationsInfo info, BindingResult result, @PathVariable String cri) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String phone2 = cri.split(",")[0];
        String belonger = cri.split(",")[1];
        if ("没人".equals(belonger) || belonger == "没人") {
            info.setBelonger("");
        } else {
            info.setBelonger(belonger);
        }
        String phone = info.getCustomerPhone();
        if (phone2 != null && !"phone2".equals(phone2)) {
            info.setCustomerPhone2(phone2);
        }
        int count = reservationsService.getCustomerPhoneCounts(info.getCustomerPhone()).size();
        if (!"本人".equals(info.getPhoneSharShip())) {
            if (info.getCustomerPhone().indexOf("+") != -1) {
            } else {
                info.setCustomerPhone(info.getCustomerPhone() + "+" + count);
            }
        }
        //保存修改判断修改后的电话1和电话2是否已经存在
        if (reservationsService.getUpdatePhoneCount(info) > 0 || reservationsService.getUpdatePhoneCount2(info) > 0) {
            return 0;
        } else {
            ReservationsInfo reservationsInfo = reservationsService.selectOneById(info.getId());
            reservationsService.update(info);
            reservationsService.updatesc(info);
            String sql = "INSERT INTO `reservations_update_info` (`update_people`, `update_time`, `update_cname`, `update_cphone`,`archiving`,`filing_time`,`content`) VALUES " +
                    "('" + ShiroKit.getUser().getAccount() + "' , '" + DateTimeKit.now() + "', '" + info.getCustomerName() + "', '" + info.getCustomerPhone() + "', '" + info.getArchiving() + "'" +
                    ", '" + info.getFilingTime() + "', '" + reservationsInfo.getAppointmentPayService() + "修改为" + info.getAppointmentPayService() + "');";
            return sqlMapper.insert(sql);
        }
    }

    /**
     * 修改
     */
    @RequestMapping(value = "/adminUpdate")
    @ResponseBody
    public int adminUpdate(@Valid ReservationsInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String phone = info.getCustomerPhone();
        int count = reservationsService.getCustomerPhoneCounts(info.getCustomerPhone()).size();
        if (!"本人".equals(info.getPhoneSharShip())) {
            info.setCustomerPhone(info.getCustomerPhone() + "+" + count);
        }
        if (reservationsService.getUpdatePhoneCount(info) > 0) {
            return 0;
        } else {
            info.setCustomerPhone(phone);
            reservationsService.adminUpdate(info);
            reservationsService.reservationsUpdate(info);
            return 1;
        }
    }

    @RequestMapping(value = "/addChatRecord")
    @ResponseBody
    public int addChatRecord(@Valid ReservationsInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String updatesql = "update reservations_info a set a.content='" + info.getContent() + "' where id='" + info.getId() + "'";
        sqlMapper.update(updatesql);
        return 1;
    }

    @RequestMapping(value = "/customer_import")
    public String to_import(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "customer_import.html";
    }

    @RequestMapping(value = "/reservationsExport")
    public void reservationsExport(@RequestParam(required = false) String customerName, @RequestParam(required = false) String status,
                                   @RequestParam(required = false) String customerPhone, @RequestParam(required = false) String appointmentPayService,
                                   @RequestParam(required = false) String startRevisitTime, @RequestParam(required = false) String endRevisitTime,
                                   @RequestParam(required = false) String archiving, @RequestParam(required = false) String accessDisease,
                                   @RequestParam(required = false) String startFilingTime, @RequestParam(required = false) String endFilingTime,
                                   @RequestParam(required = false) String outpatientName, @RequestParam(required = false) String startArrivalTime,
                                   @RequestParam(required = false) String endArrivalTime, @RequestParam(required = false) String oneChannel,
                                   @RequestParam(required = false) String twoChannel, @RequestParam(required = false) String isReturnVisit,
                                   @RequestParam(required = false) String thrChannel, @RequestParam(required = false) String reservationsNum,
                                   @RequestParam(required = false) String wechatNumber, @RequestParam(required = false) String statuss,
                                   @RequestParam(required = false) String tcName, @RequestParam(required = false) String department,
                                   HttpServletResponse response) {
        try {
            SearchReservationsInfo searchInfo = new SearchReservationsInfo();
            searchInfo.setCustomerName(customerName);
            searchInfo.setStatus(status);
            searchInfo.setStatuss(statuss);
            searchInfo.setTcName(tcName);
            searchInfo.setCustomerPhone(customerPhone);
            searchInfo.setAppointmentPayService(appointmentPayService);
            searchInfo.setStartRevisitTime(startRevisitTime);
            searchInfo.setArchiving(archiving);
            searchInfo.setAccessDisease(accessDisease);
            searchInfo.setStartFilingTime(startFilingTime);
            searchInfo.setEndFilingTime(endFilingTime);
            searchInfo.setOutpatientName(outpatientName);
            searchInfo.setStartArrivalTime(startArrivalTime);
            searchInfo.setEndArrivalTime(endArrivalTime);
            searchInfo.setOneChannel(oneChannel);
            searchInfo.setTwoChannel(twoChannel);
            searchInfo.setThrChannel(thrChannel);
            searchInfo.setReservationsNum(reservationsNum);
            searchInfo.setIsReturnVisit(Integer.parseInt(isReturnVisit));
            searchInfo.setWechatNumber(wechatNumber);
            searchInfo.setDepartment(department);
            List<ReservationsInfo> list = reservationsService.reservationsExport(searchInfo);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
            for (int i = 0; i < 19; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("姓名");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("性别");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("年龄");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("手机号");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("微信号");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("建档人");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("一级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("二级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("三级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("预约门诊");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("到店状态");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("预约病种");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("访问病种");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("建档时间");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("首次到店时间");
            cell.setCellStyle(style);
            cell = row.createCell(15);
            cell.setCellValue("开单状态");
            cell.setCellStyle(style);
            cell = row.createCell(16);
            cell.setCellValue("成交金额");
            cell.setCellStyle(style);
            cell = row.createCell(17);
            cell.setCellValue("初诊tc");
            cell.setCellStyle(style);
            cell = row.createCell(18);
            cell.setCellValue("消费项目");
            cell.setCellStyle(style);
            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                ReservationsInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                customerName = map.getCustomerName();
                String sex = map.getSex();
                int customerAge = 0;
                if (map.getCustomerAge() != null) {
                    customerAge = map.getCustomerAge();
                }
                customerPhone = map.getCustomerPhone();
                wechatNumber = map.getWechatNumber();
                archiving = map.getArchiving();
                oneChannel = map.getOneChannel();
                twoChannel = map.getTwoChannel();
                thrChannel = map.getThrChannel();
                outpatientName = map.getOutpatientName();
                status = map.getStatus();
                tcName = map.getTcName();
                appointmentPayService = map.getAppointmentPayService();
                String filingTime = map.getFilingTime();
                String firstVisitDate = map.getFirstVisitDate();
                String dealStatus = map.getDealStatus();
                String accessDiseases = map.getAccessDisease();
                Double money = map.getMoney();
                if (money == null) {
                    money = 0.00;
                }
                String payServiceName = "";
                if (StringUtil.isNotEmpty(map.getPayServiceName())) {
                    payServiceName = map.getPayServiceName();
                }
                String id = map.getId();
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 19; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(customerName);
                row.getCell(1).setCellValue(sex);
                row.getCell(2).setCellValue(customerAge);
                row.getCell(3).setCellValue(customerPhone);
                row.getCell(4).setCellValue(wechatNumber);
                row.getCell(5).setCellValue(archiving);
                row.getCell(6).setCellValue(oneChannel);
                row.getCell(7).setCellValue(twoChannel);
                row.getCell(8).setCellValue(thrChannel);
                row.getCell(9).setCellValue(outpatientName);
                row.getCell(10).setCellValue(status);
                row.getCell(11).setCellValue(appointmentPayService);
                row.getCell(12).setCellValue(accessDiseases);
                row.getCell(13).setCellValue(filingTime);
                row.getCell(14).setCellValue(firstVisitDate);
                row.getCell(15).setCellValue(dealStatus);
                row.getCell(16).setCellValue(money);
                row.getCell(17).setCellValue(tcName);
                row.getCell(18).setCellValue(payServiceName);
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}